﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace xlsSql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.openFileDialog1.InitialDirectory = System.Windows.Forms.Application.StartupPath;
        }


        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            this.fileName = this.textBox1.Text.Trim();
            this.outName = this.textBox2.Text.Trim();
            this.dataFileName = this.textBox4.Text.Trim();
            if (this.outName.Length == 0)
            {
                this.textBox2.Text = Path.GetFileNameWithoutExtension(this.fileName) + ".sql";
            }
            if (this.dataFileName.Length == 0)
            {
                this.textBox4.Text = "data_init.xls";
            }
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            this.outName = this.textBox2.Text.Trim();
        }

        private void textBox4_TextChanged(object sender, EventArgs e)
        {
            if (this.textBox4.Text.Trim().Length > 0)
            {
                this.dataFileName = Path.GetDirectoryName(this.fileName) + @"\" + this.textBox4.Text.Trim();
                this.button5.Visible = true;
            }
            else
            {
                this.button5.Visible = false;
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                this.textBox1.Text = this.openFileDialog1.FileName;
            }
        }



        private void button2_Click(object sender, EventArgs e)
        {
            CheckForIllegalCrossThreadCalls = false;
            MethodInvoker invoker = new MethodInvoker(Run);
            invoker.BeginInvoke(null, null);
        }


        bool stop = false;
        private void Stop_Click(object sender, EventArgs e)
        {
            stop = true;
        }

        private void ButtenEnabled(bool b)
        {
            button1.Enabled = b;
            button2.Enabled = b;
            button5.Enabled = b;
            button6.Enabled = b;
        }

        private bool IsStop(bool b)
        {
            if (b)
            {
                MessageBox.Show("中断");
                ButtenEnabled(true);
            }
            return b;
        }

        private void Run()
        {
            ButtenEnabled(false);
            if ((this.fileName.Length == 0) || (this.outName.Length == 0))
            {
                MessageBox.Show(this, "错误: 请输入文件名!");
                if (this.fileName.Length == 0)
                {
                    this.textBox1.Focus();
                }
                else
                {
                    this.textBox2.Focus();
                }
            }
            else
            {
                StreamWriter writer;
                try
                {
                    writer = File.CreateText(this.outName);
                }
                catch
                {
                    MessageBox.Show(this, "错误: 输出文件建立错误!");
                    return;
                }
                if (File.Exists(this.fileName))
                {
                    object missing = Type.Missing;
                    Microsoft.Office.Interop.Excel.Application application = new ApplicationClass();
                    if (application == null)
                    {
                        MessageBox.Show(this, "错误: " + this.fileName + "文件不能打开!");
                        writer.Close();
                    }
                    else
                    {
                        int num2;
                        Worksheet worksheet;
                        Workbook workbook = application.Workbooks.Open(this.fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                        int num = 0;
                        int num3 = workbook.Worksheets.Count;
                        string str = "";
                        this.textBox3.Clear();
                        if (IsStop(stop))
                            return; //中断
                        if (checkBox1.Checked)
                        {
                            for (int i = 1; i <= num3; i++)
                            {
                                if (IsStop(stop))
                                    return; //中断

                                worksheet = (Worksheet)workbook.Worksheets[i];
                                num = worksheet.UsedRange.Cells.Rows.Count;
                                num2 = (worksheet.UsedRange.Cells.Row > 1) ? (worksheet.UsedRange.Cells.Row - 1) : worksheet.UsedRange.Cells.Row;
                                str = ((Range)worksheet.UsedRange.Cells[1, 1]).Text.ToString();
                                this.textBox3.AppendText(string.Format("-- 删除 {0} --\r\n\r\n", str));
                                this.textBox3.AppendText(string.Format("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\r\ndrop table [dbo].[{0}]\r\nGO\r\n\r\n", str));

                            }
                        }
                        if (checkBox2.Checked)
                        {
                            for (int j = 1; j <= num3; j++)
                            {
                                if (IsStop(stop))
                                    return; //中断

                                worksheet = (Worksheet)workbook.Worksheets[j];
                                num = worksheet.UsedRange.Cells.Rows.Count;
                                num2 = (worksheet.UsedRange.Cells.Row > 1) ? (worksheet.UsedRange.Cells.Row - 1) : worksheet.UsedRange.Cells.Row;
                                str = ((Range)worksheet.UsedRange.Cells[1, 1]).Text.ToString();
                                string str2 = ((Range)worksheet.UsedRange.Cells[1, 6]).Text.ToString().Trim();
                                this.textBox3.AppendText(string.Format("-- {0} \r\n-- {1}\r\n\r\n", str, ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString()));

                                //string exe

                                this.textBox3.AppendText(string.Format("CREATE TABLE [dbo].[{0}] (\r\n", str));
                                for (int k = 3; k < (num + num2); k++)
                                {
                                    string str3 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString().Trim();
                                    string description = ((Range)worksheet.UsedRange.Cells[k, 2]).Text.ToString().Trim();
                                    if (str3.Length != 0)
                                    {
                                        string str4 = ((Range)worksheet.UsedRange.Cells[k, 3]).Text.ToString().Trim().ToLower();
                                        string str5 = ((Range)worksheet.UsedRange.Cells[k, 4]).Text.ToString().Trim();
                                        string str6 = ((Range)worksheet.UsedRange.Cells[k, 5]).Text.ToString().Trim();
                                        if (k != 3)
                                        {
                                            this.textBox3.AppendText(",\r\n");
                                        }
                                        if (str6.ToUpper() == "ID")
                                        {
                                            this.textBox3.AppendText(string.Format("\t[{0}] [int] IDENTITY (1, 1) NOT NULL ", str3));
                                        }
                                        else
                                        {
                                            if (str6.Length > 0)
                                            {
                                                str6 = "default " + str6 + " ";
                                            }
                                            int index = str4.IndexOf("(");
                                            if ((str4.IndexOf("char") >= 0) || (str4.IndexOf("text") >= 0))
                                            {
                                                if (index > 0)
                                                {
                                                    str4 = string.Format(" [{0}] {1}", str4.Substring(0, index), str4.Substring(index));
                                                }
                                                else if (str4.IndexOf("char") >= 0)
                                                {
                                                    str4 = string.Format(" [{0}] (50)", str4);
                                                }
                                                else
                                                {
                                                    str4 = string.Format(" [{0}]", str4);
                                                }
                                            }
                                            else if (str4.IndexOf("binary") >= 0)
                                            {
                                                if (index > 0)
                                                {
                                                    str4 = string.Format(" [{0}] {1}", str4.Substring(0, index), str4.Substring(index));
                                                }
                                                else
                                                {
                                                    str4 = string.Format(" [{0}] (50)", str4);
                                                }
                                            }
                                            else if (str4.IndexOf("decimal") >= 0)
                                            {
                                                if (index > 0)
                                                {
                                                    str4 = string.Format(" [{0}] {1}", str4.Substring(0, index), str4.Substring(index));
                                                }
                                                else
                                                {
                                                    str4 = string.Format(" [{0}] (18, 0)", str4);
                                                }
                                            }
                                            else if (str4.IndexOf("numeric") >= 0)
                                            {
                                                if (index > 0)
                                                {
                                                    str4 = string.Format(" [{0}] {1}", str4.Substring(0, index), str4.Substring(index));
                                                }
                                                else
                                                {
                                                    str4 = string.Format(" [{0}] (18, 0)", str4);
                                                }
                                            }
                                            else if (str4.Length == 0)
                                            {
                                                this.textBox3.AppendText("\t-- >>>>>>  错误: 类型未标明，默认为int <<<<<< --\r\n");
                                                str4 = " [int]";
                                            }
                                            else
                                            {
                                                str4 = " [" + str4 + "]";
                                            }
                                            str5 = (str5.Length == 0) ? " NULL" : " NOT NULL";
                                            this.textBox3.AppendText(string.Format("\t[{0}]{1}{2} {3}", new object[] { str3, str4, str5, str6 }));
                                        }
                                    }
                                }
                                if (str2.Length > 0)
                                {
                                    this.textBox3.AppendText(string.Format(",\r\n\tCONSTRAINT PK_{0} PRIMARY KEY ({1})", str.Substring(str.IndexOf('_') + 1), str2));
                                }
                                this.textBox3.AppendText("\r\n) ON [PRIMARY]");
                                this.textBox3.AppendText("\r\nGO\r\n\r\n");


                                //添加备注

                                string cmdText = "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'";
                                for (int k = 3; k < (num + num2); k++)
                                {
                                    if (IsStop(stop))
                                        return; //中断

                                    string bstr2 = ((Range)worksheet.UsedRange.Cells[k, 2]).Text.ToString().Trim();
                                    string bstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString().Trim();

                                    if (bstr2.Trim() != "" && bstr1.Trim() != "")
                                    {
                                        this.textBox3.AppendText(cmdText);
                                        this.textBox3.AppendText(bstr2);
                                        this.textBox3.AppendText("' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'");
                                        this.textBox3.AppendText(str);
                                        this.textBox3.AppendText("', @level2type=N'COLUMN',@level2name=N'");
                                        this.textBox3.AppendText(bstr1);
                                        this.textBox3.AppendText("'");
                                        this.textBox3.AppendText("\r\nGO\r\n\r\n");
                                    }
                                }
                                string tablememo = ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString();
                                if (tablememo.Trim() != "")
                                {
                                    this.textBox3.AppendText(cmdText);
                                    this.textBox3.AppendText(tablememo);
                                    this.textBox3.AppendText(string.Format("' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{0}'", str));
                                    this.textBox3.AppendText("\r\nGO\r\n\r\n");
                                }
                            }
                        }
                        if (checkBox3.Checked)
                        {
                            //添加关系
                            for (int j = 1; j <= num3; j++)
                            {
                                if (IsStop(stop))
                                    return; //中断

                                worksheet = (Worksheet)workbook.Worksheets[j];
                                num = worksheet.UsedRange.Cells.Rows.Count;
                                num2 = (worksheet.UsedRange.Cells.Row > 1) ? (worksheet.UsedRange.Cells.Row - 1) : worksheet.UsedRange.Cells.Row;
                                str = ((Range)worksheet.UsedRange.Cells[1, 1]).Text.ToString();
                                bool t = true;

                                for (int k = 3; k < (num + num2); k++)
                                {
                                    string gstr6 = ((Range)worksheet.UsedRange.Cells[k, 6]).Text.ToString().Trim();

                                    if (gstr6.Trim() != "")
                                    {
                                        if (t)
                                        {
                                            this.textBox3.AppendText(string.Format("-- 创建关系 \r\n-- {0} \r\n-- {1}\r\n\r\n", str, ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString()));
                                            t = false;
                                        }
                                        string gstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString().Trim();
                                        string table = "";
                                        string tkey = "";
                                        try
                                        {
                                            table = gstr6.Substring(0, gstr6.IndexOf("("));
                                            tkey = gstr6.Substring(gstr6.IndexOf("(") + 1, gstr6.IndexOf(")") - gstr6.IndexOf("(") - 1);
                                        }
                                        catch
                                        {
                                            this.textBox3.AppendText(string.Format("\t-- >>>>>>  错误: 关系 {0} 格式错误 <<<<<< --\r\n", gstr6));
                                            continue;
                                        }
                                        this.textBox3.AppendText(string.Format("ALTER TABLE [dbo].[{0}]  WITH CHECK ADD  CONSTRAINT [FK_{1}_{2}] FOREIGN KEY([{3}])", str, str, table, gstr1));
                                        this.textBox3.AppendText("\r\n");
                                        this.textBox3.AppendText(string.Format("REFERENCES [dbo].[{0}] ([{1}])", table, tkey));
                                        if (gstr6.Substring(gstr6.Length - 1, 1) != ")")
                                        {
                                            switch (gstr6.Substring(gstr6.Length - 1, 1).ToUpper())
                                            {
                                                case "*": this.textBox3.AppendText("\r\nON UPDATE CASCADE\r\nON DELETE CASCADE");
                                                    break;
                                                case "D": this.textBox3.AppendText("\r\nON DELETE CASCADE");
                                                    break;
                                                case "U": this.textBox3.AppendText("\r\nON UPDATE CASCADE");
                                                    break;
                                                case "N": this.textBox3.AppendText("\r\nON UPDATE CASCADE\r\nON DELETE SET NULL");
                                                    break;
                                                default:
                                                    break;
                                            }

                                        }
                                        this.textBox3.AppendText("\r\nGO\r\n");
                                        this.textBox3.AppendText(string.Format("ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [FK_{1}_{2}]", str, str, table));
                                        this.textBox3.AppendText("\r\nGO\r\n\r\n");
                                    }
                                }
                            }
                        }
                        //添加 CREATE,UPDATE,DELETE 存储过程
                        if (checkBox5.Checked)
                        {
                            string temp = "";
                            for (int j = 1; j <= num3; j++)
                            {
                                if (IsStop(stop))
                                    return; //中断



                                //CREATE
                                worksheet = (Worksheet)workbook.Worksheets[j];
                                num = worksheet.UsedRange.Cells.Rows.Count;
                                num2 = (worksheet.UsedRange.Cells.Row > 1) ? (worksheet.UsedRange.Cells.Row - 1) : worksheet.UsedRange.Cells.Row;
                                str = ((Range)worksheet.UsedRange.Cells[1, 1]).Text.ToString();

                                this.textBox3.AppendText(string.Format("-- 删除存储过程 \r\n-- {0}_ADD \r\n ", str));
                                this.textBox3.AppendText(string.Format("if exists (select * from sysobjects where id = object_id(N'[dbo].[{0}_ADD]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)", str));
                                this.textBox3.AppendText("\r\n");
                                this.textBox3.AppendText(string.Format("drop procedure [dbo].[{0}_ADD]\r\n\r\n", str));

                                this.textBox3.AppendText("SET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n");
                                this.textBox3.AppendText(string.Format("-- 创建存储过程 \r\n-- {0}_ADD \r\n-- {1}\r\n\r\n", str, ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString()));
                                this.textBox3.AppendText(string.Format("CREATE PROCEDURE [dbo].[{0}_ADD]\r\n\r\n", str));

                                for (int k = 3; k < (num + num2); k++)
                                {
                                    if (IsStop(stop))
                                        return; //中断

                                    string pstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString();
                                    string pstr3 = ((Range)worksheet.UsedRange.Cells[k, 3]).Text.ToString();
                                    string pstr5 = ((Range)worksheet.UsedRange.Cells[k, 5]).Text.ToString();
                                    if (pstr1.Trim() != "" && pstr3.Trim() != "")
                                    {
                                        temp += (string.Format("\r\n@{0} {1} ", pstr1, pstr3));
                                        if (pstr5.Trim() != "" && pstr5.Trim().ToUpper() != "ID")
                                        {
                                            temp += " = ";
                                            temp += pstr5;
                                        }
                                        temp += ",";
                                    }
                                }
                                this.textBox3.AppendText(temp.Substring(0, temp.Length - 1));
                                temp = "";
                                this.textBox3.AppendText("\r\n\r\nAS\r\n\r\nBEGIN\r\n\r\n");
                                this.textBox3.AppendText("insert into ");
                                this.textBox3.AppendText(str);
                                this.textBox3.AppendText("(");
                                for (int k = 3; k < (num + num2); k++)
                                {
                                    string pstr5 = ((Range)worksheet.UsedRange.Cells[k, 5]).Text.ToString();
                                    if (pstr5.ToUpper() != "ID")
                                    {
                                        string pstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString();
                                        if (pstr1.Trim() != "")
                                        {
                                            temp += pstr1;
                                            temp += ",";
                                        }
                                    }
                                }
                                this.textBox3.AppendText(temp.Substring(0, temp.Length - 1));
                                temp = "";
                                this.textBox3.AppendText(") values(");
                                for (int k = 3; k < (num + num2); k++)
                                {
                                    if (IsStop(stop))
                                        return; //中断

                                    string pstr5 = ((Range)worksheet.UsedRange.Cells[k, 5]).Text.ToString();
                                    if (pstr5.ToUpper() != "ID")
                                    {
                                        string pstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString();
                                        if (pstr1.Trim() != "")
                                        {
                                            temp += "@";
                                            temp += pstr1;
                                            temp += ",";
                                        }
                                    }
                                }
                                this.textBox3.AppendText(temp.Substring(0, temp.Length - 1));
                                temp = "";
                                this.textBox3.AppendText(") \r\n\r\nEND\r\n\r\nGO\r\n\r\n");


                                //UPDATE
                                if (((Range)worksheet.UsedRange.Cells[1, 6]).Text.ToString().Trim() != "")
                                {
                                    string[] primes = ((Range)worksheet.UsedRange.Cells[1, 6]).Text.ToString().Trim().Split(',');

                                    this.textBox3.AppendText(string.Format("-- 删除存储过程 \r\n-- {0}_Update \r\n ", str));
                                    this.textBox3.AppendText(string.Format("if exists (select * from sysobjects where id = object_id(N'[dbo].[{0}_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)", str));
                                    this.textBox3.AppendText("\r\n");
                                    this.textBox3.AppendText(string.Format("drop procedure [dbo].[{0}_Update]\r\n\r\n", str));

                                    this.textBox3.AppendText("SET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n");
                                    this.textBox3.AppendText(string.Format("-- 创建存储过程 \r\n-- {0}_Update \r\n-- {1}\r\n\r\n", str, ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString()));
                                    this.textBox3.AppendText(string.Format("CREATE PROCEDURE [dbo].[{0}_Update]\r\n\r\n", str));

                                    for (int k = 3; k < (num + num2); k++)
                                    {
                                        if (IsStop(stop))
                                            return; //中断

                                        string pstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString();
                                        string pstr3 = ((Range)worksheet.UsedRange.Cells[k, 3]).Text.ToString();
                                        if (pstr1.Trim() != "" && pstr3.Trim() != "")
                                        {
                                            temp += (string.Format("\r\n @{0} {1},", pstr1, pstr3));
                                        }
                                    }
                                    this.textBox3.AppendText(temp.Substring(0, temp.Length - 1));
                                    temp = "";
                                    this.textBox3.AppendText("\r\n\r\nAS\r\n\r\nBEGIN\r\n\r\n");
                                    this.textBox3.AppendText("update ");
                                    this.textBox3.AppendText(str);
                                    this.textBox3.AppendText(" set ");
                                    for (int k = 3; k < (num + num2); k++)
                                    {
                                        if (IsStop(stop))
                                            return; //中断

                                        string pstr5 = ((Range)worksheet.UsedRange.Cells[k, 5]).Text.ToString();
                                        if (pstr5.ToUpper() != "ID")
                                        {
                                            string pstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString();
                                            if (pstr1.Trim() != "")
                                            {
                                                bool b = false;
                                                foreach (string var in primes)
                                                {
                                                    if (pstr1 == var)
                                                    {
                                                        b = true;
                                                        break;
                                                    }
                                                }
                                                if (!b)
                                                    temp += (string.Format(" {0}=@{1},", pstr1, pstr1));
                                            }
                                        }
                                    }
                                    this.textBox3.AppendText(temp.Substring(0, temp.Length - 1));
                                    temp = "";
                                    string pstr1z = ((Range)worksheet.UsedRange.Cells[1, 6]).Text.ToString();
                                    if (pstr1z.IndexOf(",") < 0)
                                    {
                                        this.textBox3.AppendText(string.Format("\r\nwhere {0}=@{0} ", pstr1z));
                                    }
                                    else
                                    {
                                        string strwhere = "\r\nwhere";
                                        foreach (string var in primes)
                                        {
                                            strwhere += string.Format(" {0}=@{0} and", var.Trim());
                                        }
                                        this.textBox3.AppendText(strwhere.TrimEnd("and".ToCharArray()));
                                    }
                                    this.textBox3.AppendText("\r\n\r\nEND\r\n\r\nGO\r\n\r\n");
                                }
                                ////DELETE
                                //this.textBox3.AppendText(string.Format("-- 删除存储过程 \r\n-- DELETE_{0} \r\n ", str));
                                //this.textBox3.AppendText(string.Format("if exists (select * from sysobjects where id = object_id(N'[dbo].[DELETE_{0}]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)", str));
                                //this.textBox3.AppendText("\r\n");
                                //this.textBox3.AppendText(string.Format("drop procedure [dbo].[DELETE_{0}]\r\n\r\n", str));

                                //this.textBox3.AppendText("SET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n");
                                //this.textBox3.AppendText(string.Format("-- 创建存储过程 \r\n-- DELETE_{0} \r\n-- {1}\r\n\r\n", str, ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString()));
                                //this.textBox3.AppendText(string.Format("CREATE PROCEDURE [dbo].[DELETE_{0}]\r\n\r\n", str));

                                //if (IsStop(stop))
                                //    return; //中断

                                //if (pstr1z.IndexOf(",") < 0)
                                //{
                                //    for (int k = 3; k < (num + num2); k++)
                                //    {
                                //        string pstr1 = ((Range)worksheet.UsedRange.Cells[k, 1]).Text.ToString();
                                //        if (pstr1 == pstr1z)
                                //        {
                                //            string pstr3 = ((Range)worksheet.UsedRange.Cells[k, 3]).Text.ToString();
                                //            if (pstr1.Trim() != "" && pstr3.Trim() != "")
                                //            {
                                //                this.textBox3.AppendText(string.Format("\r\n @{0} {1}", pstr1, pstr3));
                                //            }
                                //        }
                                //    }
                                //}
                                //else
                                //{
                                //    this.textBox3.AppendText("--@name1 type1,\r\n--@name2 type2");
                                //}
                                //this.textBox3.AppendText("\r\n\r\nAS\r\n\r\nBEGIN\r\n\r\n");
                                //this.textBox3.AppendText("delete from ");
                                //this.textBox3.AppendText(str);
                                //if (pstr1z.IndexOf(",") < 0)
                                //{
                                //    this.textBox3.AppendText(string.Format("\r\nwhere {0}=@{1} ", pstr1z, pstr1z));
                                //}
                                //else
                                //{
                                //    this.textBox3.AppendText("\r\n--nwhere 范围");
                                //}
                                //this.textBox3.AppendText("\r\n\r\nEND\r\n\r\nGO\r\n\r\n");


                                //if (IsStop(stop))
                                //    return; //中断

                                ////DELETES
                                //this.textBox3.AppendText(string.Format("-- 删除存储过程 \r\n-- DELETES_{0} \r\n ", str));
                                //this.textBox3.AppendText(string.Format("if exists (select * from sysobjects where id = object_id(N'[dbo].[DELETES_{0}]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)", str));
                                //this.textBox3.AppendText("\r\n");
                                //this.textBox3.AppendText(string.Format("drop procedure [dbo].[DELETES_{0}]\r\n\r\n", str));

                                //this.textBox3.AppendText("SET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\n");
                                //this.textBox3.AppendText(string.Format("-- 创建存储过程 \r\n-- DELETE_{0} \r\n-- {1}\r\n\r\n", str, ((Range)worksheet.UsedRange.Cells[1, 2]).Text.ToString()));
                                //this.textBox3.AppendText(string.Format("CREATE PROCEDURE [dbo].[DELETES_{0}]\r\n\r\n", str));

                                //if (pstr1z.IndexOf(",") < 0)
                                //{
                                //    this.textBox3.AppendText("\r\n@qualification VARCHAR(MAX)");
                                //    this.textBox3.AppendText("\r\n\r\nAS\r\n\r\nBEGIN\r\n\r\n   Declare @i int;\r\n   Declare @Start int;\r\n   Declare @Length int;\r\n   Declare @TotalLenght int;\r\n\r\n   Set @i = 1;\r\n   Set @Start = 1;\r\n   Set @Length = 0;\r\n   Set @TotalLenght = Len(@Qualification);\r\n\r\n   While(@i <= @TotalLenght)\r\n   Begin\r\n      If(SubString(@Qualification, @i, 1) = ',')\r\n      Begin\r\n");
                                //    this.textBox3.AppendText(string.Format("         Delete {0} Where {1} = SubString(@Qualification, @Start, @Length);\r\n", str, pstr1z));
                                //    this.textBox3.AppendText("         Set @Start = @i + 1;\r\n         Set @Length = 0;\r\n      End\r\n      Else\r\n      Begin\r\n         Set @Length = @Length + 1;\r\n      End\r\n      Set @i = @i + 1;\r\n   End\r\n   If(Right(@Qualification, 1) <> ',')\r\n   Begin\r\n");
                                //    this.textBox3.AppendText(string.Format("         Delete {0} Where {1} = SubString(@Qualification, @Start, @Length);\r\n", str, pstr1z));
                                //    this.textBox3.AppendText("   End\r\nEnd\r\nGo\r\n\r\n");
                                //}
                                //else
                                //{
                                //    this.textBox3.AppendText("\r\n @qualification VARCHAR(MAX)");
                                //    this.textBox3.AppendText("\r\n\r\nAS\r\n\r\nBEGIN\r\n");
                                //    this.textBox3.AppendText("--代码\r\nEnd\r\nGo\r\n\r\n");
                                //}
                                //if (IsStop(stop))
                                //    return; //中断

                            }
                        }

                        if (checkBox4.Checked)
                        {
                            if (File.Exists(this.dataFileName))
                            {
                                workbook = application.Workbooks.Open(this.dataFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                                int num8 = 0;
                                string format = "\r\n-- 数据初始化 {0} 表\r\nINSERT {0} ({1})\r\nSELECT {1}\r\nFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=\"{2}\";User ID=Admin;Password=;Extended properties=Excel 5.0')...[{0}$]\r\nGO\r\n";
                                for (int m = 1; m <= workbook.Worksheets.Count; m++)
                                {
                                    worksheet = (Worksheet)workbook.Worksheets[m];
                                    num8 = worksheet.UsedRange.Cells.Columns.Count;
                                    if (worksheet.UsedRange.Cells.Rows.Count >= 1)
                                    {
                                        string str8 = "";
                                        for (int n = 1; n <= num8; n++)
                                        {
                                            str8 = str8 + string.Format("[{0}],", ((Range)worksheet.UsedRange.Cells[1, n]).Text.ToString().Trim().ToLower());
                                        }
                                        str8 = str8.Trim(new char[] { ',' });
                                        this.textBox3.AppendText(string.Format(format, worksheet.Name, str8, this.dataFileName));
                                    }
                                }
                            }
                            if (IsStop(stop))
                                return; //中断

                        }
                        application.Quit();
                        writer.Write(this.textBox3.Text);
                        writer.Close();
                    }
                }
                else
                {
                    MessageBox.Show(this, "错误: " + this.fileName + "文件不存在!");
                    writer.Close();
                }
            }
            ButtenEnabled(true);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            base.Close();
        }
        private void button6_Click(object sender, EventArgs e)
        {
            textBox3.SelectAll();
            textBox3.Copy();
            MessageBox.Show("复制完成");
        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void ToolStripMenuItem1_Click(object sender, EventArgs e)
        {

        }

        private void Exit_ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            base.Close();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            using (SaveFileDialog obj = new SaveFileDialog())
            {
                obj.Filter = "*.xls|*.xls";
                if (obj.ShowDialog() == DialogResult.OK)
                {
                    System.IO.File.WriteAllBytes(obj.FileName, Properties.Resources.新建_Microsoft_Excel_工作表);
                }
            }
        }


    }
}